#!/bin/bash
# Copyright (c) 2017 ~ 2025, the original author wangl.sir individual Inc,
# All rights reserved. Contact us <wanglsir@gmail.com, 983708408@qq.com>
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# see: https://github.com/databacker/mysql-backup
# see: https://github.com/iredmail/iRedMail/blob/master/tools/backup_mysql.sh

# Load environments.
export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:
. /etc/profile

# Global definition.
export dbHost=${DB_BACKUP_HOST:-127.0.0.1}
export dbPort=${DB_BACKUP_PORT:-3066}
export dbUser=${DB_BACKUP_USER:-'root'}
export dbPassword=${DB_BACKUP_PASSWORD:-'123456'}
export dbName=${DB_BACKUP_NAME:-wordpress}
export dbCharset="utf8"
export mysqlCmd="$MYSQL_HOME/bin/mysql"
export mysqlDumpCmd="$MYSQL_HOME/bin/mysqldump"
export mysqlBinlogCmd="$MYSQL_HOME/bin/mysqlbinlog"
export backupExcludeTables=('user' 'event')
export backupMaxFiles=${DB_BACKUP_MAX_FILES:-100} # And condition 'backupMaxRetainDays' as long as any one is satisfied.
export backupMaxRetainDays=${DB_BACKUP_MAX_RETAIN_DAYS:-7}
export backupDir="${DB_BACKUP_DIR:-/mnt/disk1/mysql/backups}"; mkdir -p $backupDir
export backupLogFile="$backupDir/backup.log"
export backupLogMaxBytes=$((1*1024*1024*1024)) # 1GB
export backupCurrentFile="${backupDir}/${dbName}-$(date +%Y%m%d_%H%M%S).sql"

# Preflight check.
[ ! -d "$MYSQL_HOME" ] && echo "Please set environment of MYSQL_HOME." | tee -a "$backupLogFile" && exit -1

function getDumpExtraOpts() {
    #local mysqldVersion=$('SELECT VERSION()' | ${mysqlCmd} -h${dbHost} -u${dbUser} -p${dbPassword} 2>/dev/null | grep -v 'VERSION()')
    #
    # Notice: The cmmand line arg '--column-statistics=0' is added to solve the problem of mysqldump: Couldn't execute 'SELECT COLUMN_NAME ...
    # The reason is that mysqldump is incompatible with mysqld, such as mysqldump/8.x and mysqld/5.x
    if [ -n "$($mysqlDumpCmd --version | grep 'Ver 8.')" ]; then # mysqldump 8.x
        local extraOpts='--column-statistics=0'
    fi
    # Add exclude tables.
    for tname in ${backupExcludeTables[@]}; do
      local extraOpts=$extraOpts" --ignore-table=${dbName}.${tname}"
    done
    echo $extraOpts
}

function doBackupDumpFull() {
  # Check commands.
  if [ -z "$mysqlDumpCmd" ]; then
    echo "$(date +%Y-%m-%d_%H:%M:%S) - command 'mysqldump' not found" | tee -a "$backupLogFile"; exit 2
  fi

  # Dump backup files.
  local extraOpts=$(getDumpExtraOpts) 
  echo "$(date +%Y-%m-%d_%H:%M:%S) - Dumping exec command: $mysqlDumpCmd --default-character-set=${dbCharset} -F ${extraOpts} -h${dbHost} -P${dbPort} -u${dbUser} -p'******' ${dbName} > ${backupCurrentFile} 2>> ${backupLogFile} 2>&1 | tee -a $backupLogFile"

  /bin/bash -c "$mysqlDumpCmd -F ${extraOpts} -h${dbHost} -P${dbPort} -u${dbUser} -p'${dbPassword}' ${dbName} > ${backupCurrentFile} 2>> ${backupLogFile} 2>&1 | tee -a $backupLogFile"

  if [ ${PIPESTATUS[0]} -eq 0 ]; then
    echo "$(date +%Y-%m-%d_%H:%M:%S) - Successful dump to $backupCurrentFile from $dbUser@$dbHost/$dbName" | tee -a "$backupLogFile"
  else
    echo "$(date +%Y-%m-%d_%H:%M:%S) - Failed to dump from $dbUser@$dbHost/$dbName" | tee -a "$backupLogFile"
  fi

  doCleanOldBackupDump
}

function doCleanOldBackupDump() {
  # Check older dump.
  echo "$(date +%Y-%m-%d_%H:%M:%S) - Cleaning older backup dump files ..." | tee -a "$backupLogFile"

  # Remove by max files.
  ls "$backupDir" -lt|awk '{if(NR > '$backupMaxFiles'){print "echo $(date +%Y-%m-%d_%H:%M:%S) - [DEBUG] Deleting old backup file: '$backupDir'/"$9" ...; rm -rf '$backupDir'/"$9}}' | sh | tee -a "$backupLogFile"

  # Remove by max retain days.
  find $backupDir -mtime +${backupMaxRetainDays} -name "*.sql" -exec rm -rf {} \;

  # Remove backuplog.
  local logSize=$(ls -l $backupLogFile | awk '{ print $5 }')
  if [ $logSize -gt $backupLogMaxBytes ]; then
    echo > "$backupLogFile"
    echo "$(date +%Y-%m-%d_%H:%M:%S) - Cleaned backup log." | tee -a "$backupLogFile"
  fi
}

# function doBackupBinlogIncr() {
#   # Check commands.
#   if [ -n "$mysqlBinlogCmd" ]; then
#     echo "$(date +%Y-%m-%d_%H:%M:%S) - command 'mysqlbinlog' not found" | tee -a "$backupLogFile"; exit 2
#   fi
#   echo '$(date +%Y-%m-%d_%H:%M:%S) - No supported operation !!!' | tee -a "$backupLogFile"
#   exit 1
# }

# function doRestoreBackupDump() {
#   #$mysqlBinlogCmd --start-datetime="2017-08-31 00:00:00" --stop-datetime="2017-08-31 12:00:00" mysql_bin.000002 | mysql -u${dbUser} -p'${dbPassword}'
#   echo '$(date +%Y-%m-%d_%H:%M:%S) - No supported operation !!!' | tee -a "$backupLogFile"
#   exit 1
# }

case $1 in
  -f|--full)
    doBackupDumpFull
  ;;
  -c|--cleanup)
    doCleanOldBackupDump
  ;;
#   -b|--binlogIncr)
#     doBackupBinlogIncr
#   ;;
#   -r|--restore)
#     doRestoreBackupDump
#   ;;
  -h|--help|*)
    echo -e "\nUsages: ./$(basename $0) [OPTIONS] [ARGS]
    -f, --full  Use mysqldump to export all the specified database tables to SQL files.
    -c, --cleanup Cleaning olders backup sql files (by 'backupMaxFiles' or 'backupMaxRetainDays').
    -h, --help print help information.
    " >/dev/stderr; exit 1
  ;;
esac